<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
  <title id="ei20941">SET</title>
  <body>
    <p id="sql_command_desc">Changes the value of a Greenplum Database configuration parameter.</p>
    <section id="section2">
      <title>Synopsis</title>
      <codeblock id="sql_command_synopsis">SET [SESSION | LOCAL] <varname>configuration_parameter</varname> {TO | =} <varname>value</varname> | 
    '<varname>value</varname>' | DEFAULT}

SET [SESSION | LOCAL] TIME ZONE {<varname>timezone</varname> | LOCAL | DEFAULT}</codeblock>
    </section>
    <section id="section3">
      <title>Description</title>
      <p>The <codeph>SET</codeph> command changes server configuration parameters. Any configuration
        parameter classified as a <varname>session</varname> parameter can be changed on-the-fly
        with <codeph>SET</codeph>. <codeph>SET</codeph> affects only the value used by the current
        session. </p>
      <p>If <codeph>SET</codeph> or <codeph>SET SESSION</codeph> is issued within a transaction that
        is later cancelled, the effects of the <codeph>SET</codeph> command disappear when
        the transaction is rolled back. Once the surrounding transaction is committed, the effects
        will persist until the end of the session, unless overridden by another
        <codeph>SET</codeph>. </p>
      <p>The effects of <codeph>SET LOCAL</codeph> last only till the end of the current
        transaction, whether committed or not. A special case is <codeph>SET</codeph> followed by
          <codeph>SET LOCAL</codeph> within a single transaction: the <codeph>SET LOCAL</codeph>
        value will be seen until the end of the transaction, but afterwards (if the transaction is
        committed) the <codeph>SET</codeph> value will take effect.</p>
      <p>If <codeph>SET LOCAL</codeph> is used within a function that includes a
          <codeph>SET</codeph> option for the same configuration parameter (see <codeph><xref
            href="./CREATE_FUNCTION.xml#topic1" type="topic" format="dita"/></codeph>), the effects
        of the <codeph>SET LOCAL</codeph> command disappear at function exit; the value in effect
        when the function was called is restored anyway. This allows <codeph>SET LOCAL</codeph> to
        be used for dynamic or repeated changes of a parameter within a function, while retaining
        the convenience of using the <codeph>SET</codeph> option to save and restore the caller's
        value. Note that a regular <codeph>SET</codeph> command overrides any surrounding function's
          <codeph>SET</codeph> option; its effects persist unless rolled back.</p>
      <p>If you create a cursor with the <codeph>DECLARE</codeph> command in a transaction, you
        cannot use the <codeph>SET</codeph> command in the transaction until you close the cursor
        with the <codeph>CLOSE</codeph> command.</p>
      <p>See <xref href="../config_params/guc_config.xml" type="topic" format="dita"/> for
        information about server parameters.</p>
    </section>
    <section id="section4">
      <title>Parameters</title>
      <parml>
        <plentry>
          <pt>SESSION</pt>
          <pd>Specifies that the command takes effect for the current session. This is the
            default.</pd>
        </plentry>
        <plentry>
          <pt>LOCAL</pt>
          <pd>Specifies that the command takes effect for only the current transaction. After
              <codeph>COMMIT</codeph> or <codeph>ROLLBACK</codeph>, the session-level setting takes
            effect again. Note that <codeph>SET LOCAL</codeph> will appear to have no effect if it
            is run outside of a transaction.</pd>
        </plentry>
        <plentry>
          <pt>
            <varname>configuration_parameter</varname>
          </pt>
          <pd>The name of a Greenplum Database configuration parameter. Only parameters classified
            as <i>session</i> can be changed with <codeph>SET</codeph>. See <xref
              href="../config_params/guc_config.xml" type="topic" format="dita"/> for details.</pd>
        </plentry>
        <plentry>
          <pt>
            <varname>value</varname>
          </pt>
          <pd>New value of parameter. Values can be specified as string constants, identifiers,
            numbers, or comma-separated lists of these. <codeph>DEFAULT</codeph> can be used to
            specify resetting the parameter to its default value. If specifying memory sizing or
            time units, enclose the value in single quotes.</pd>
        </plentry>
        <plentry>
          <pt>TIME ZONE</pt>
          <pd><codeph>SET TIME ZONE</codeph> value is an alias for <codeph>SET timezone TO
                <varname>value</varname></codeph>. The syntax <codeph>SET TIME ZONE</codeph> allows
            special syntax for the time zone specification. Here are examples of valid values: </pd>
          <pd>
            <codeph>'PST8PDT'</codeph>
          </pd>
          <pd>
            <codeph>'Europe/Rome'</codeph>
          </pd>
          <pd><codeph>-7</codeph> (time zone 7 hours west from UTC)</pd>
          <pd><codeph>INTERVAL '-08:00' HOUR TO MINUTE</codeph> (time zone 8 hours west from
            UTC).</pd>
        </plentry>
        <plentry>
          <pt>LOCAL</pt>
          <pt>DEFAULT</pt>
          <pd>Set the time zone to your local time zone (that is, server's default value of <varname>timezone</varname>). See the <xref
              href="https://www.postgresql.org/docs/9.4/datatype-datetime.html#DATATYPE-TIMEZONES"
              scope="external" format="html">Time zone section of the PostgreSQL
              documentation</xref> for more information about time zones in Greenplum Database.</pd>
        </plentry>
      </parml>
    </section>
    <section id="section5">
      <title>Examples</title>
      <p>Set the schema search path:</p>
      <codeblock>SET search_path TO my_schema, public;</codeblock>
      <p>Increase the segment host memory per query to 200 MB:</p>
      <codeblock>SET statement_mem TO '200MB';</codeblock>
      <p>Set the style of date to traditional POSTGRES with "day before month" input convention:</p>
      <codeblock>SET datestyle TO postgres, dmy;</codeblock>
      <p>Set the time zone for San Mateo, California (Pacific Time):</p>
      <codeblock>SET TIME ZONE 'PST8PDT';</codeblock>
      <p>Set the time zone for Italy:</p>
      <codeblock>SET TIME ZONE 'Europe/Rome'; </codeblock>
    </section>
    <section id="section6">
      <title>Compatibility</title>
      <p><codeph>SET TIME ZONE</codeph> extends syntax defined in the SQL standard. The standard
        allows only numeric time zone offsets while Greenplum Database allows more flexible
        time-zone specifications. All other <codeph>SET</codeph> features are Greenplum Database
        extensions. </p>
    </section>
    <section id="section7">
      <title>See Also</title>
      <p><codeph><xref href="RESET.xml#topic1" type="topic" format="dita"/></codeph>, <codeph><xref
            href="./SHOW.xml#topic1" type="topic" format="dita"/></codeph></p>
    </section>
  </body>
</topic>
